package com.doublefish.doublefishrobot.utils.db.database;

import com.doublefish.doublefishrobot.utils.db.database.bean.TableInfoModel;

import net.sqlcipher.Cursor;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 作者:dugaofeng on 2024/10/16 14:38
 */
public class SQLiteSentenceMakeHelper {

    /**
     * 生成 数量查询的 sql
     * @param tableName  表名
     * @param tableInfoModel 所有表结构信息
     * @param data 查询条件
     * @return sql语句
     */
    public static String makeQueryCountSql(String tableName, TableInfoModel tableInfoModel, Map<String,Object> data) {
//        SELECT count(*) from tableName  where ...;

        //生成where语句
        String where = SQLiteSentencMakeUtil.makeQueryWhere(tableInfoModel.getFieldsOfTable(tableName),data);
        return  "SELECT count(*) from " + tableName + where;
    }

    /**
     * 生成查询的sql语句
     * @param tableName  表名
     * @param tableInfoModel  所有表结构信息
     * @param data  查询条件
     * @return
     */
    public static String makeQuerySql(String tableName,TableInfoModel tableInfoModel, Map<String,Object> data) {
//        SELECT* from tableName  where ...;
        //生成where语句
        String where = SQLiteSentencMakeUtil.makeQueryWhere(tableInfoModel.getFieldsOfTable(tableName),data);
        return  "SELECT * from " + tableName + where;
    }



    /**
     * 生成 插入的 sql
     * @param tableName  表名
     * @param tableInfoModel 所有表结构信息
     * @param data 要插入的数据
     * @return sql语句
     */
    public static String makeInsertSql(String tableName,TableInfoModel tableInfoModel, Map<String,Object> data) {
//        INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN);

        StringBuilder fields = new StringBuilder();
        StringBuilder values = new StringBuilder();
        for (String key:data.keySet()) {
            int fieldType = getFieldType(tableInfoModel.getFieldsOfTable(tableName),key);
            switch (fieldType) {
                case Cursor.FIELD_TYPE_INTEGER:
                case Cursor.FIELD_TYPE_FLOAT:
                    fields.append(key).append(",");
                    values.append(data.get(key)).append(",");
                    break;
                case Cursor.FIELD_TYPE_NULL:
                    //不包含该字段
                    break;

                default:
                    if (data.get(key) != null) {
                        String str = sqliteEscape((String) data.get(key));
                        fields.append(key).append(",");
                        values.append("'").append(str).append("',");
                    }
                    break;
            }
        }

        //去掉最后一个 ","
        String fieldsStr = fields.toString();
        if (fieldsStr.length() > 0)
            fieldsStr = fieldsStr.substring(0,fieldsStr.length() - 1);

        String valuesStr = values.toString();
        if (valuesStr.length() > 0)
            valuesStr = valuesStr.substring(0,valuesStr.length() - 1);

        //构造sql
        return  "INSERT INTO " + tableName + "(" + fieldsStr + ") values (" + valuesStr + ");";
    }

    /**
     * 批量生成 插入的 sql
     * @param tableName  表名
     * @param tableInfoModel 所有表结构信息
     * @param list 要插入的数据
     * @return sql语句
     */
    public static List<String> makeInsertSql(String tableName, TableInfoModel tableInfoModel, List<Map<String, Object>> list) {
        List<String> sqlList = new ArrayList<>();
        for (Map<String,Object> map: list) {
            String sql =  makeInsertSql(tableName, tableInfoModel, map);
            sqlList.add(sql);
        }
        return sqlList;
    }

    /**
     * 删除数据的sql
     * @param tableName  表名
     * @param tableInfoModel 所有表结构信息
     * @param data 要删除数据的条件
     * @return
     */
    public static String makeDeleteSql(String tableName,TableInfoModel tableInfoModel, Map<String,Object> data) {
//        DELETE FROM table_name WHERE [condition];
        if (data == null || data.size() == 0) {
            return "DELETE FROM " + tableName + ";";
        }

        //生成where语句
        String where = SQLiteSentencMakeUtil.makeQueryWhere(tableInfoModel.getFieldsOfTable(tableName),data);
        return  "DELETE FROM " + tableName + where;
    }

    /**
     * 更新表数据的sql
     * @param tableName 表名
     * @param tableInfoModel 所有表结构信息
     * @param data       要更新的数据
     * @param whereData   更新的条件
     * @return
     */
    public static String makeUpdateSql(String tableName,TableInfoModel tableInfoModel, Map<String,Object> data ,Map<String,Object> whereData) {
//        UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];

        if (data == null || data.size() == 0) {
            return "";
        }

        StringBuilder keyValuePairs = new StringBuilder();
        for (String key:data.keySet()) {
            int fieldType = getFieldType(tableInfoModel.getFieldsOfTable(tableName), key);
            switch (fieldType) {
                case Cursor.FIELD_TYPE_INTEGER:
                case Cursor.FIELD_TYPE_FLOAT:
                    keyValuePairs.append(key).append("=").append(data.get(key)).append(",");
                    break;

                case Cursor.FIELD_TYPE_NULL:
                    //不包含该字段
                    break;

                default:
                    if (data.get(key) != null) {
                        String str = sqliteEscape((String) data.get(key));
                        keyValuePairs.append(key).append("=").append("'").append(str).append("',");
                    }
                    break;
            }
        }


        String pairsStr = keyValuePairs.toString();
        if (pairsStr.length() == 0)
            return "";
        else {
            //去掉最后一个逗号
            pairsStr = pairsStr.substring(0,pairsStr.length() - 1);
        }

        String where = "";
        if (whereData == null || whereData.size() == 0) {
            //没有where条件
        } else {
            where = SQLiteSentencMakeUtil.makeQueryWhere(tableInfoModel.getFieldsOfTable(tableName),whereData);
        }
        return  "UPDATE " + tableName + " SET " + pairsStr +  where;
    }


    /**
     * 返回字段对应的类型
     * @param fieldOfTable 表的字段信息
     * @param fieldName   字段名
     * @return   字段对应的类型
     */
    private static int getFieldType(Map<String,Integer> fieldOfTable,String fieldName) {
        if (fieldOfTable.containsKey(fieldName)) {
            return fieldOfTable.get(fieldName);
        }
        return Cursor.FIELD_TYPE_NULL; //不包含该字段
    }


    /**
     *  对部分特殊字符进行处理
     * @param keyWord
     * @return
     */
    public static String sqliteEscape(String keyWord){
//        keyWord = keyWord.replace("/", "//");
        keyWord = keyWord.replace("'", "''");
//        keyWord = keyWord.replace("[", "/[");
//        keyWord = keyWord.replace("]", "/]");
//        keyWord = keyWord.replace("%", "/%");
//        keyWord = keyWord.replace("?", "/?");
//        keyWord = keyWord.replace("&","/&");
//        keyWord = keyWord.replace("_", "/_");
//        keyWord = keyWord.replace("(", "/(");
//        keyWord = keyWord.replace(")", "/)");
        return keyWord;
    }

}